﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ColetaDAL
    Public ObjConexao As New Conexao

    Public Function RetItens(Codigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "select *from vw_Coleta where col_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = True
            Return tab
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function validaItens(Codigo As Integer, EleCodigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "select *from Mul_Coleta_Eletronico where col_codigo = @codigo and ele_codigo=@elecodigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@elecodigo", SqlDbType.Int)).Value = EleCodigo
            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = True
            Return tab
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function InsereColeta(ColCodigo As Integer, Data As DateTime, CodPessoa As Integer, Nome As String) As Integer
        Dim Codigo As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "execute SPColeta @colcodigo,@data,@pescodigo, @nome "
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.Date)).Value = Data
                cmd.Parameters.Add(New SqlParameter("@pescodigo", SqlDbType.Int)).Value = CodPessoa
            cmd.Parameters.Add(New SqlParameter("@colcodigo", SqlDbType.Int)).Value = ColCodigo
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = Nome

            If ColCodigo = 0 Then
                Codigo = ObjConexao.executaCmdAI(cmd, "Mul_Coleta")
            Else
                ObjConexao.executaCmd(cmd)
            End If


            'If ColCodigo = 0 Then
            '    cmd.CommandText = "Insert into Mul_coleta(col_data,pes_codigo) values(@data,@pescodigo);"
            '    cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.DateTime)).Value = Data
            '    cmd.Parameters.Add(New SqlParameter("@pescodigo", SqlDbType.Int)).Value = CodPessoa
            '    Codigo = ObjConexao.executaCmdAI(cmd, "Mul_Coleta")
            'Else
            '    cmd.CommandText = "update Mul_coleta set col_data=@data ,pes_codigo=@pescodigo where col_codigo=@colcodigo;"
            '    cmd.Parameters.Add(New SqlParameter("@colcodigo", SqlDbType.Int)).Value = ColCodigo
            '    cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.DateTime)).Value = Data.ToString
            '    cmd.Parameters.Add(New SqlParameter("@pescodigo", SqlDbType.Int)).Value = CodPessoa
            '    Codigo = ObjConexao.executaCmd(cmd)
            'End If


            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub InsereItens(ColCodigo As Integer, EleCodigo As Integer, CelQtde As Integer, Unidade As Integer)
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "Insert into Mul_Coleta_Eletronico(col_codigo,ele_codigo,cel_qtde,uni_codigo) values(@colcodigo,@elecodigo,@celqtde,@unidade);"
            cmd.Parameters.Add(New SqlParameter("@colcodigo", SqlDbType.Int)).Value = ColCodigo
            cmd.Parameters.Add(New SqlParameter("@elecodigo", SqlDbType.Int)).Value = EleCodigo
            cmd.Parameters.Add(New SqlParameter("@celqtde", SqlDbType.Int)).Value = CelQtde
            cmd.Parameters.Add(New SqlParameter("@unidade", SqlDbType.Int)).Value = Unidade
            ObjConexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub AlteraItens(ColCodigo As Integer, EleCodigo As Integer, CelQtde As Integer, Unidade As Integer)
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "update Mul_Coleta_Eletronico set ele_codigo=@elecodigo,cel_qtde=@celqtde,uni_codigo=@unidade where col_codigo=@colcodigo"
            cmd.Parameters.Add(New SqlParameter("@colcodigo", SqlDbType.Int)).Value = ColCodigo
            cmd.Parameters.Add(New SqlParameter("@elecodigo", SqlDbType.Int)).Value = EleCodigo
            cmd.Parameters.Add(New SqlParameter("@celqtde", SqlDbType.Int)).Value = CelQtde
            cmd.Parameters.Add(New SqlParameter("@unidade", SqlDbType.Int)).Value = Unidade
            ObjConexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub ExcluiItens(ColCodigo As Integer, EleCodigo As Integer)
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "delete from Mul_Coleta_Eletronico where  col_codigo=@colcodigo and ele_codigo=@elecodigo"
            cmd.Parameters.Add(New SqlParameter("@colcodigo", SqlDbType.Int)).Value = ColCodigo
            cmd.Parameters.Add(New SqlParameter("@elecodigo", SqlDbType.Int)).Value = EleCodigo
            ObjConexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#Region "Buscar"
    Public Function Buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_Coletas where col_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_Coletas where pes_nome COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_Coletas order by col_codigo"
            End Select

            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
End Class
